/**************************************************************

This do file imports the raw CFO excel files and cleans them

**************************************************************/
capture clear
capture log close
set more off

global rawdata 
global geodata 
global cleandata 

***************************************************************; 

set excelxlsxlargefile on

forvalues year = 1988(1)2014 {
	import excel using "${rawdata}Emigrant Data from 1988to2014 for Amherst College.xlsx", firstrow sheet("`year'") case(lower)
	save "${rawdata}data`year'", replace
	clear
	}
	
forvalues w=1988/2014{;
use "${rawdata}data`w'.dta", clear;

generate count1 = _N;

*creates birth_muni and birth_prov;
generate regular = 1 if strrpos(birthplace,"(") == strpos(birthplace,"(");
replace birthplace = regexr(birthplace,"\(","[") if regular!=1;
generate temp = strpos(birthplace,"(");
generate birth_muni = substr(birthplace,1,temp-1);
generate temp2 = length(birthplace)-temp;
generate birth_prov = substr(birthplace,temp+1,temp2-1);

replace birth_prov = birthplace if temp==0; /* added this to original code because some provinces cut off in last letter*/

replace birth_muni = substr(birth_muni,-length(birth_muni),length(birth_muni)-2);
replace birth_prov = "FIRST DISTRICT" if strpos(birth_prov,"NCR")>0 & strpos(birth_prov,"FIRST")>0;
replace birth_prov = "SECOND DISTRICT" if strpos(birth_prov,"NCR")>0 & strpos(birth_prov,"SECOND")>0;
replace birth_prov = "THIRD DISTRICT" if strpos(birth_prov,"NCR")>0 & strpos(birth_prov,"THIRD")>0;
replace birth_prov = "FOURTH DISTRICT" if strpos(birth_prov,"NCR")>0 & strpos(birth_prov,"FOURTH")>0;
replace birth_prov = "NO RESPONSE" if strpos(birthplace,"NO RESPONS")>0;
replace birth_muni = "NO RESPONSE" if birth_prov=="NO RESPONSE";
replace birthplace = regexr(birthplace,"\[","(") if regular!=1;
replace birth_muni = regexr(birth_muni,"\[","(") if regular !=1;
replace birth_muni = "MIDSAYAP" if birth_muni=="MIDSAYAP,";
drop temp temp2;

*creates muni and province;
generate regular2 = 1 if strrpos(addressinthephilippines,"(") == strpos(addressinthephilippines,"(");
replace addressinthephilippines = regexr(addressinthephilippines,"\(","[") if regular2 !=1;
generate temp = strpos(addressinthephilippines,"(");
generate muni = substr(addressinthephilippines,1,temp-1);
generate temp2 = length(addressinthephilippines)-temp;
generate province = substr(addressinthephilippines,temp+1,temp2-1);

replace province = addressinthephilippines if temp==0; /* added this to original code because some provinces cut off in last letter*/

replace muni = substr(muni,-length(muni),length(muni)-2);
replace province = "FIRST DISTRICT" if strpos(province,"NCR")>0 & strpos(province,"FIRST")>0;
replace province = "SECOND DISTRICT" if strpos(province,"NCR")>0 & strpos(province,"SECOND")>0;
replace province = "THIRD DISTRICT" if strpos(province,"NCR")>0 & strpos(province,"THIRD")>0;
replace province = "FOURTH DISTRICT" if strpos(province,"NCR")>0 & strpos(province,"FOURTH")>0;
replace province = "NO RESPONSE" if strpos(province,"NO RESPONS")>0;
replace muni = "NO RESPONSE" if province=="NO RESPONSE";
replace addressinthephilippines = regexr(addressinthephilippines,"\[","(") if regular2 !=1;
replace muni = regexr(muni,"\[","(") if regular2 !=1;
replace muni = "MIDSAYAP" if muni=="MIDSAYAP,";
drop temp temp2;

*remove odd parentheticals, generate vars to store them; 
generate capi = regexm(birth_muni,"\(");
generate capi2 = regexm(muni,"\(");
generate paren = strpos(birth_muni,"(") if regular != 1 & capi == 1;
generate endparen = strpos(birth_muni,")") if regular != 1 & capi == 1;
generate birth_parens = substr(birth_muni,paren+1,endparen-paren-1) if regular2 != 1 & capi2 == 1;
replace birth_muni = substr(birth_muni,1,paren-2) if paren != 0;
generate paren2 = strpos(muni,"(") if regular2 != 1 & capi2 ==1;
generate endparen2 = strpos(muni,")") if regular2 != 1 & capi2 == 1;
generate address_parens = substr(muni,paren2+1,endparen2-paren2-1) if regular2 != 1 & capi2 == 1;
replace muni = substr(muni,1,paren2-2) if paren2 != 0;
drop paren paren2 capi capi2 endparen endparen2;

replace muni = "MANILA" if prov=="FIRST DISTRICT";
replace birth_muni = "MANILA" if birth_prov=="FIRST DISTRICT";
foreach x in "muni" "birth_muni"{;
	replace `x' = substr(`x',1,-1) if substr(`x',-1,.)==",";
	replace `x' = substr(`x',1,-1) if substr(`x',-1,.)==" ";
	replace `x' = substr(`x',9,.) if substr(`x',1,8)=="CITY OF " & substr(`x',9,.)!="FERNANDO";
	replace `x' = "DASMARINAS" if `x'=="DASMARIÑAS";
	replace `x' = "ANTIPOLO CITY" if `x'=="ANTIPOLO";
	replace `x' = "BINAN" if `x'=="BIÑAN";
	replace `x' = "CAGAYAN DE ORO" if `x'=="CAGAYAN DE ORO CITY";
	replace `x' = "CALAMBRA CITY" if `x'=="CALAMBRA" & (prov=="LAGUNA" | birth_prov=="LAGUNA");
	replace `x' = "GEN. TRIAS" if `x'=="GENERAL TRIAS";
	replace `x' = "LAS PINAS" if `x'=="LAS PIÑAS";
	replace `x' = "LOS BANOS" if `x'=="LOS BAÑOS";
	replace `x' = "MALOLOS CITY" if `x'=="MALOLOS ";
	replace `x' = "MEYCAUAYAN CITY" if `x'=="MEYCAUAYAN";
	replace `x' = "PANABO CITY" if `x'=="PANABO";
	replace `x' = "PARANAQUE" if `x'=="PARAÑAQUE";
	replace `x' = "PASAY" if `x'=="PASAY CITY";
	replace `x' = "SAN FERNANDO CITY" if `x'=="SAN FERNANDO" & (prov=="LA UNION" | birth_prov=="LA UNION");
	replace `x' = "SAN JOSE DEL MONTE CITY" if `x'=="SAN JOSE DEL MONTE";
	replace `x' = "SANTA ROSA CITY" if `x'=="SANTA ROSA" & (prov=="LAGUNA" | birth_prov=="LAGUNA");
	replace `x' = "SANTIAGO CITY" if `x'=="SANTIAGO" & (prov=="ISABELA" | birth_prov=="ISABELA");
	replace `x' = "TAGUM CITY" if `x'=="TAGUM";
	replace `x' = "TALISAY CITY" if `x'=="TALISAY" & (prov=="CEBU" | birth_prov=="CEBU");
	replace `x' = "TANAUAN CITY" if `x'=="TANAUAN" & (prov=="BATANGAS" | birth_prov=="BATANGAS");
	replace `x' = "TARLAC CITY" if `x'=="TARLAC";
	replace `x' = "TUGUEGARAO CITY" if `x'=="TUGUEGARAO";
	replace `x' = "URDANETA CITY" if `x'=="URDANETA";
	replace `x' = "VIGAN CITY" if `x'=="VIGAN";
	replace `x' = "BATAC CITY" if `x'=="BATAC";
	replace `x' = "CALAMBA CITY" if `x'=="CALAMBA" & (prov=="LAGUNA" | birth_prov=="LAGUNA");
	replace `x' = "MALOLOS CITY" if `x'=="MALOLOS";
	replace `x' = "CALOOCAN" if `x'=="KALOOKAN CITY";
	replace `x' = "CAUAYAN CITY" if `x'=="CAUAYAN" & (birth_prov=="ISABELA"|prov=="ISABELA");
	replace `x' = "ALAMINOS CITY" if `x'=="PANGASINAN" & (birth_prov=="PANGASINAN"|prov=="PANGASINAN");
	replace `x' = "CITY OF SAN FERNANDO" if `x'=="SAN FERNANDO" & (birth_prov=="PAMPANGA" | prov == "PAMPANGA");
	replace `x' = "ALAMINOS CITY" if `x'=="ALAMINOS" & (birth_prov=="PANGASINAN" | prov=="PANGASINAN");
	replace `x' = "MALAYBALAY CITY" if `x'=="CITY MALAYBALAY";
	replace `x' = "SCIENCE CITY OF MUNOZ" if `x'=="MUÑOZ";
	replace `x' = "NAGA CITY" if `x'=="NAGA" & (birth_prov=="CAMARINES SUR" | prov=="CAMARINES SUR" | birth_prov=="CEBU" | prov=="CEBU");
	replace `x' = "MAASIN CITY" if `x'=="MAASIN" & (birth_prov=="SOUTHERN LEYTE" | prov=="SOUTHERN LEYTE");
	replace `x' = "KIDAPAWAN CITY" if `x'=="KIDAPAWAN";
	replace `x' = "TALISAY CITY" if `x'=="TALISAY" & (birth_prov=="CEBU" | prov=="CEBU");
	replace `x' = "CATBALOGAN CITY" if `x'=="CATBALOGAN";
	replace `x' = "MASBATE CITY" if `x'=="MASBATE";
	replace `x' = "VALENCIA CITY" if `x'=="VALENCIA" & (birth_prov=="BUKIDNON" | prov=="BUKIDNON");
	replace `x' = "KABANKALAN CITY" if `x'=="KABANKALAN";
	replace `x' = "TALISAY CITY" if `x'=="TALISAY" & (birth_prov=="NEGROS OCCIDENTAL" | prov=="NEGROS OCCIDENTAL");
	replace `x' = "M'LANG" if `x'=="M`LANG";
	replace `x' = "BOGO CITY" if `x'=="BOGO";
	replace `x' = "PENARANDA" if `x'=="PEÑARANDA";
	replace `x' = "BAIS" if `x'=="BAIS CITY";
	replace `x' = "SANTO NINO" if `x'=="SANTO NIÑO";
	replace `x' = "CANLAON" if `x'=="CANLAON CITY";
	replace `x' = "PENABLANCA" if `x'=="PEÑABLANCA";
	replace `x' = "ISABELA CITY" if `x'=="ISABELA" & (prov=="BASILAN"|birth_prov=="BASILAN");
	replace `x' = "ALAMINOS" if `x'=="ALAMINOS CITY" & (prov=="LAGUNA"|birth_prov=="LAGUNA");
	replace `x' = "DUENAS" if `x'=="DUEÑAS";
	replace `x' = "TALISAY" if `x'=="TALISAY CITY" & (prov=="BATANGAS"|birth_prov=="BATANGAS");
	replace `x' = "PENARRUBIA" if `x'=="PEÑARRUBIA";
	replace `x' = "GEN. EMILIO AGUINALDO" if `x'=="GENERAL EMILIO AGUINALDO";
	replace `x' = "TALISAY CITY" if `x' == "TALISAY" & (prov=="CEBU"|birth_prov=="CEBU");
	replace `x' = "ALAMINOS CITY" if `x' == "ALAMINOS" & (prov=="PANGASINAN"|birth_prov=="PANGASINAN");
	replace `x' = "LA PAZ" if `x' == "LAPAZ" & (prov=="LEYTE"|birth_prov=="LEYTE");
	replace `x' = "SAGNAY" if `x' == "SAGÑAY";
	replace `x' = "LIBJO (ALBOR)" if `x'=="LIBJO";
	replace `x' = "TALISAY" if `x'=="TALISAY CITY" & (prov=="BATANGAS"|birth_prov=="BATANGAS");
	replace `x' = "TALISAY" if `x'=="TALISAY CITY" & (prov=="CAMARINES NORTE"|birth_prov=="CAMARINES NORTE");
	replace `x' = "T'BOLI" if `x'=="T BOLI";
	replace `x' = "BROOKE'S POINT" if `x'=="BROOKE`S POINT";
	replace `x' = "PINAN" if `x'=="PIÑAN";
};

replace prov = "SAMAR" if muni=="PARANAS";
replace birth_prov = "SAMAR" if birth_muni=="PARANAS";
replace prov = "ZAMBOANGA SIBUGAY" if muni=="SIAY";
replace birth_prov = "ZAMBOANGA SIBUGAY" if birth_muni=="SIAY";
replace prov = "ZAMBOANGA SIBUGAY" if muni=="KABASALAN";
replace birth_prov = "ZAMBOANGA SIBUGAY" if birth_muni=="KABASALAN";
replace prov = "ZAMBOANGA SIBUGAY" if muni=="BUUG";
replace birth_prov = "ZAMBOANGA SIBUGAY" if birth_muni=="BUUG";
replace prov = "SAMAR" if muni=="CATBALOGAN CITY";
replace birth_prov = "SAMAR" if birth_muni=="CATBALOGAN CITY";
replace prov = "COTABATO" if muni=="PIGKAWAYAN";
replace birth_prov = "COTABATO" if birth_muni=="PIGKAWAYAN";
replace prov = "COTABATO" if muni=="TULUNAN";
replace birth_prov = "COTABATO" if birth_muni=="TULUNAN";
replace prov = "DINAGAT ISLANDS" if muni=="DINAGAT";
replace birth_prov = "DINAGAT ISLANDS" if birth_muni=="DINAGAT";
replace prov = "COTABATO" if muni=="MAKILALA";
replace birth_prov = "COTABATO" if birth_muni=="MAKILALA";
replace prov = "ZAMBOANGA SIBUGAY" if muni=="IPIL";
replace birth_prov = "ZAMBOANGA SIBUGAY" if birth_muni=="IPIL";
replace prov = "COTABATO" if prov=="NORTH COTABATO";
replace birth_prov = "COTABATO" if birth_prov == "NORTH COTABATO";
replace prov = "SECOND DISTRICT" if muni=="MADALUYONG";
replace birth_prov = "SECOND DISTRICT" if birth_muni=="MANDALUYONG";
replace prov = "FOURTH DISTRICT" if muni=="PARANAQUE";
replace birth_prov = "FOURTH DISTRICT" if birth_muni=="PARANAQUE";
replace prov = "SECOND DISTRICT" if muni=="QUEZON CITY";
replace birth_prov = "SECOND DISTRICT" if birth_muni=="QUEZON CITY";
replace prov = "QUEZON" if muni=="SAMPALOC";
replace birth_prov = "QUEZON" if birth_muni=="SAMPALOC";
replace prov = "CAVITE" if muni=="DASMARINAS";
replace birth_prov = "CAVITE" if birth_muni=="DASMARINAS";
replace prov = "DAVAO DEL SUR" if muni=="DAVAO CITY";
replace birth_prov = "DAVAO DEL SUR" if birth_muni=="DAVAO CITY";
replace prov = "NEGROS ORIENTAL" if muni=="DUMAGUETE";
replace birth_prov = "NEGROS ORIENTAL" if birth_muni=="DUMAGUETE";
replace prov = "PAMPANGA" if muni=="ANGELES CITY";
replace birth_prov = "PAMPANGA" if birth_muni=="ANGELES CITY";
replace prov = "RIZAL" if muni=="ANTIPOLO CITY";
replace birth_prov = "RIZAL" if birth_muni=="ANTIPOLO CITY";
replace prov = "CAVITE" if muni=="BACOOR";
replace birth_prov = "CAVITE" if birth_muni=="BACOOR";
replace prov = "CAVITE" if muni=="IMUS";
replace birth_prov = "CAVITE" if birth_muni=="IMUS";
replace prov = "FOURTH DISTRICT" if muni=="LAS PINAS";
replace birth_prov = "FOURTH DISTRICT" if birth_muni=="LAS PINAS";
replace prov = "FOURTH DISTRICT" if muni=="MAKATI";
replace prov = "QUEZON" if muni=="SAMPALOC";
replace birth_prov = "QUEZON" if birth_muni=="SAMPALOC";
replace birth_prov = "FOURTH DISTRICT" if birth_muni=="MAKATI";
replace prov = "THIRD DISTRICT" if muni=="MALABON";
replace birth_prov = "THIRD DISTRICT" if birth_muni=="MALABON";
replace prov = "THIRD DISTRICT" if muni=="VALENZUELA";
replace birth_prov = "THIRD DISTRICT" if birth_muni=="VALENZUELA";
replace prov = "CAGAYAN" if muni=="SANTA ANA";
replace birth_prov = "CAGAYAN" if birth_muni=="SANTA ANA";
replace prov = "ZAMBOANGA SIBUGAY" if muni=="MALANGAS";
replace birth_prov = "ZAMBOANGA SIBUGAY" if birth_muni=="MALANGAS";
replace prov = "ZAMBOANGA SIBUGAY" if muni=="TITAY";
replace birth_prov = "ZAMBOANGA SIBUGAY" if birth_muni=="TITAY";
replace prov = "LA UNION" if muni=="SAN FERNANDO CITY";
replace birth_prov = "LA UNION" if birth_muni=="SAN FERNANDO CITY";
replace birth_prov = "ZAMBOANGA SIBUGAY" if birth_muni=="NAGA";
replace prov = "ZAMBOANGA SIBUGAY" if muni=="NAGA";
replace prov = "DINAGAT ISLANDS" if muni=="LIBJO (ALBOR)";
replace birth_prov = "DINAGAT ISLANDS" if birth_muni=="LIBJO (ALBOR)";
replace muni = "ALAMINOS" if muni=="ALAMINOS CITY" & prov=="LAGUNA";
replace birth_muni = "ALAMINOS" if birth_muni=="ALAMINOS CITY" & birth_prov=="LAGUNA";
replace birth_prov = "ZAMBOANGA SIBUGAY" if birth_muni=="ALICIA";
replace prov = "ZAMBOANGA SIBUGAY" if muni=="ALICIA";

foreach y in "birth_muni" "muni"{;
replace `y' = "ANTIPOLO CITY" if `y'=="ANTIPOLO";
replace `y' = "MUNTINLUPA" if `y'=="AYALA ALABANG";
replace `y'="CALOOCAN" if `y'=="BAGUMBONG/PAG-ASA";
replace `y'="BALANGA CITY" if `y'=="BALANGA";
replace `y'="MAKATI" if `y'=="BANGKAL";
replace `y'="PARANAQUE" if `y'=="BETTER LIVING SUBDIVISION";
replace `y' = "CAGAYAN DE ORO" if `y'=="CAGAYAN DE ORO CITY";
replace `y' = "QUEZON CITY" if `y'=="CUBAO";
replace `y' = "DARAGA" if `y'=="DARAGA (Locsin)";
replace `y' = "CAGAYAN DE ORO" if `y'=="CAGAYAN DE ORO CITY";

generate temp=regexm(`y', "^DASMARI.*AS$");
replace `y'="DASMARINAS" if temp==1;
drop temp;

replace `y' = "TAGUIG CITY" if `y'=="FORT BONAFACIO";
replace `y' = "TAGUIG CITY" if `y'=="FORT BONIFACIO";
replace `y' = "TAGUIG" if `y'=="TAGUIG CITY";
replace `y'="GEN. TRIAS" if `y'=="GEN TRIAS";
replace `y'="QUEZON CITY" if `y'=="KAMUNING";
replace `y'="QUEZON CITY" if `y'=="LIBIS";
replace `y'="MARAWI" if `y'=="MARAWI CITY";
replace `y'="MARIKINA" if `y'=="MARKINA HEIGHTS";
replace `y'="PASIG" if `y'=="MAYBUNGA";
replace `y' = "MEYCAUAYAN CITY" if `y'=="MEYCAUAYAN";
replace `y'="PARANAQUE" if `y'=="MULTINATIONAL VILLAGE";
replace `y' = "QUEZON CITY" if `y'=="NOVALICHES PROPER";
replace `y'="QUIRINO" if `y'=="PROJ 2 &3 (QUIRINO DIST)";
replace `y'="PARANAQUE" if `y'=="SAN ANTONIO VALLEY";
replace `y'="SAN JOSE DEL MONTE CITY" if `y'=="SAN JOSE DEL MONTE";
replace `y'="SANTA ANA" if `y'=="STA. ANA";
replace `y'="MARAWI CITY" if `y'=="MARAWI";
replace `y'="MARIKINA" if `y'=="MARIKINA HEIGHTS";
replace `y'="PASIG" if `y'=="PASIG CITY";

replace `y' = "QUEZON CITY" if `y'=="TANDANG SORA";

****THIS CLEANS ADDITIONAL MUNIS FOR 1995-2009****;
replace `y'="ABULUG" if `y'=="ABULOG";
replace `y' = "TAGUIG CITY" if `y'=="ACACIA"; 
replace `y'="PARANAQUE" if `y'=="AEROPARK SUBDIVISION";
replace `y'="AGDANGAN" if `y'=="AGBANGAN";
replace `y'="PATEROS" if `y'=="AGUHO";
replace `y'="ALBURQUERQUE" if `y'=="ALBUQUERQUE";

replace `y'="LAS PINAS" if `y'=="ALMANZA";
replace `y'="QUEZON CITY" if `y'=="AMIHAN";
replace `y'="CALOOCAN CITY" if `y'=="AMPARO SUBDIVISION";
replace `y'="LAS PINAS" if `y'=="ANGELA VILLAGE";
replace `y'="ANTEQUERA" if `y'=="ANTIQUERA";
replace `y'="QUEZON CITY" if `y'=="APOLONIO SAMSON";
replace `y'="QUEZON CITY" if `y'=="ARANETA SUBDIVISION";

replace `y'="MUNTINLUPA" if `y'=="AYALA";
replace `y'="ISLAND GARDEN CITY OF SAMAL" if `y'=="BABAK";
replace `y'="QUEZON CITY" if `y'=="ARANETA SUBDIVISION";
replace `y'="PARANQUE CITY" if `y'=="BACLARAN";
replace `y'="BACOLOD-KAWALI" if `y'=="BACOLOD GRANDE";
replace `y'="SORSOGON CITY" if `y'=="BACON";
replace `y'="QUEZON CITY" if `y'=="BAESA";
replace `y'="QUEZON CITY" if `y'=="BAGBAG";
replace `y'="QUEZON CITY" if `y'=="BAGONG LIPUNAN";
replace `y'="ANTIPOLO CITY" if `y'=="BAGONG NAYON";
replace `y'="QUEZON CITY" if `y'=="BAGONG PAG-ASA";
replace `y'="CALOOCAN CITY" if `y'=="BAGONG SILANG";
replace `y'="QUEZON CITY" if `y'=="BAGON SILANGAN";
replace `y'="QUEZON CITY" if `y'=="BAGONG SILANGAN";
replace `y'="QUEZON CITY" if `y'=="BAHAY TORO";
replace `y'="VALENZUELA" if `y'=="BALANGKAS-CALOONG";
replace `y'="PANGLIMA SUGALA" if `y'=="BALIMBING";
replace `y'="QUEZON CITY" if `y'=="BALINGASA";
replace `y'="BALINGOAN" if `y'=="BALINGUAN";
replace `y'="QUEZON CITY" if `y'=="BALINTAWAK";
replace `y'="QUEZON CITY" if `y'=="BALUMBATO";
replace `y'="CALOOCAN CITY" if `y'=="BANKERS VILLAGE";
replace `y'="BANTON" if `y'=="BANTON (Jones)";
replace `y'="MARIKINA" if `y'=="BARANGKA";
replace `y'="FLORIDABLANCA" if `y'=="BASA AIRBASE";
replace `y'="BASILISA (RIZAL)" if `y'=="BASILISA (Rizal)";

replace `y'="BATAC CITY" if `y'=="BATAC";
replace `y'="BACONG" if `y'=="BACING";
replace `y'="BACOLOD-KALAWI" if `y'=="BACOLOD-KAWALI";
replace `y'="QUEZON CITY" if `y'=="BACGONG BUHAY";
replace `y'="BASILISIA (RIZAL)" if `y'=="BASILISA (RIZAL)";
replace `y'="QUEZON CITY" if `y'=="BATASAN HILLS";
replace `y'="TAGUIG" if `y'=="BAY BREEZE EXECUTIVE VILLAGE";
replace `y'="MUNTINLUPA" if `y'=="BAYANAN/PUTANAN";
replace `y'="MANILA" if `y'=="TONDO";
replace `y'="MANILA" if `y'=="ERMITA";
replace `y'="MANILA" if `y'=="MALATE";
replace `y'="KABUNTALAN" if `y'=="KABUNTULAN";
replace `y'="MALOLOS CITY" if `y'=="MALOLOS";
replace `y'="GENERAL SANTOS CITY" if `y'=="GEN. SANTOS CITY";
replace `y'="TARLAC CITY" if `y'=="TARLAC";
replace `y'="TUGUGARAO CITY" if `y'=="TUGUEGARAO";
replace `y'="SANTA CRUZ" if `y'=="STA. CRUZ";
replace `y'="CALOOCAN" if `y'=="CALOOCAN CITY";
replace `y'="TAGUIG" if `y'=="BICUTAN LOWER"|`y'=="BICUTAN UPPER"|`y'=="BICUTAN";
replace `y'="MANILA" if `y'=="STA. MESA";
replace `y'="QUEZON CITY" if `y'=="COMMONWEALTH";
replace `y'="RODRIGUEZ" if `y'=="MONTALBAN (Rodriguez)";
replace `y'="QUEZON CITY" if `y'=="BATASAN HILLS";
replace `y'="PILILLA" if `y'=="PILILIA";
replace `y'="MANILA" if `y'=="PACO";
replace `y'="MANILA" if `y'=="QUIAPO";
replace `y'="KORONADAL CITY" if `y'=="KORONADAL";
replace `y'="TAGUM CITY" if `y'=="TAGUM";
replace `y'="GEN. MARIANO ALVAREZ" if `y'=="GEN MARIANO ALVAREZ";
replace `y'="NAGUILIAN" if `y'=="NAGUILLAN";
replace `y'="URDANETA CITY" if `y'=="URDANETA";
replace `y'="PASIG" if `y'=="PINAGBUHATAN";
replace `y'="TAGUIG" if `y'=="BICUTAN WESTERN";
replace `y'="CALAPAN CITY" if `y'=="CALAPAN";
replace `y'="MATI CITY" if `y'=="MATI";
replace `y'="LAS PINAS" if `y'=="TALON";
replace `y'="QUEZON CITY" if `y'=="DILIMAN";
replace `y'="GAPAN CITY" if `y'=="GAPAN";
replace `y'="POZZORUBIO" if `y'=="POZORUBIO";
replace `y'="TRECE MARTIRES CITY" if `y'=="TRECE MARTIREZ CITY";
replace `y'="TABUK CITY" if `y'=="TABUK";
replace `y'="QUEZON CITY" if `y'=="PAYATAS";
replace `y'="TACURONG" if `y'=="TAKURONG";
replace `y'="PASIG" if `y'=="MANGGAHAN";
replace `y'="LEGAZPI CITY" if `y'=="LEGASPI CITY";
replace `y'="QUEZON CITY" if `y'=="HOLY SPIRIT";
replace `y'="QUEZON CITY" if `y'=="BAGONG BUHAY";
replace `y'="QUEZON CITY" if `y'=="BAGONG BAYAN";
replace `y'="DIGOS CITY" if `y'=="DIGOS";
replace `y'="MAKATI" if `y'=="REMBO";
replace `y'="QUEZON CITY" if `y'=="PROJECT 8";
replace `y'="BISLIG CITY" if `y'=="BISLIG";
replace `y'="QUEZON CITY" if `y'=="FAIRVIEW";
replace `y'="ISABELA CITY" if `y'=="ISABELA DE BASILAN";
replace `y'="MUNTINLUPA" if `y'=="BAYANAN/PUTATAN";
replace `y'="MAKATI" if `y'=="PEMBO";
replace `y'="PANABO CITY" if `y'=="PANABO";
replace `y'="CALOOCAN" if `y'=="NOVALICHES NORTH";
replace `y'="JALAJALA" if `y'=="JALA-JALA";
replace `y'="KIDAPAWAN CITY" if `y'=="KIDAPAWAN";
replace `y'="MAKATI" if `y'=="CEMBO";
replace `y'="QUEZON CITY" if `y'=="CULIAT";
replace `y'="LAMITAN CITY" if `y'=="LAMITAN";
replace `y'="QUEZON CITY" if `y'=="TATALON";
replace `y'="DUMAGUETE" if `y'=="DUMAGUETE CITY";
replace `y'="TAYABAS CITY" if `y'=="TAYABAS";
replace `y'="CANDON CITY" if `y'=="CANDON";
replace `y'="VALENZUELA" if `y'=="KARUHATAN";
replace `y'="CALOOCAN" if `y'=="KAUNLARAN VILLAGE";
replace `y'="TABACO CITY" if `y'=="TABACO";
replace `y'="MUNTINLUPA" if `y'=="TUNASAN";
replace `y'="MALAYBALAY CITY" if `y'=="MALAYBALAY";
replace `y'="MANILA" if `y'=="PORT AREA";
replace `y'="CARCAR CITY" if `y'=="CARCAR";
replace `y'="PASSI CITY" if `y'=="PASSI";
replace `y'="VICTORIAS CITY" if `y'=="VICTORIAS";

replace `y'="ESPERANZA" if `y'=="ESPERANZA (Ampatuan)";
replace `y'="SORSOGON CITY" if `y'=="SORSOGON";
replace `y'="CALOOCAN" if `y'=="TALA LEPROSARIUM";
replace `y'="BAYBAY CITY" if `y'=="BAYBAY";
replace `y'="KABANGKALAN CITY" if `y'=="KABANGKALAN";
replace `y'="DAANBANTAYAN" if `y'=="DAANG-BANTAYAN";
replace `y'="QUEZON CITY" if `y'=="PROJECT 4";
replace `y'="VIGAN CITY" if `y'=="VIGAN";
replace `y'="QUEZON CITY" if `y'=="PASONG TAMO";
replace `y'="CALOOCAN" if `y'=="GRACE PARK EAST";
replace `y'="PARANAQUE" if `y'=="PARANAQUE CITY";
replace `y'="LIGAO CITY" if `y'=="LIGAO";
replace `y'="PARANAQUE" if `y'=="BF HOMES";
replace `y'="HIMAMAYLAN CITY" if `y'=="HIMAMAYLAN";
replace `y'="PASAY" if `y'=="VILLAMOR AIRBASE";
replace `y'="SAN JOSE DEL MONTE CITY" if `y'=="DEL MONTE";
replace `y'="MANILA" if `y'=="BINONDO";
replace `y'="MARIKINA" if `y'=="NANGKA";
replace `y'="VALENZUELA" if `y'=="FORTUNE VIL-PASO DE BLAS-GEN T DE LEON";
replace `y'="SANTA IGNACIA" if `y'=="STA. IGNACIA";
replace `y'="MUNTINLUPA" if `y'=="MUNTINLUPA CPO";
replace `y'="CALOOCAN" if `y'=="MAYPAJO";
replace `y'="SAN JOSE DEL MONTE CITY" if `y'=="SAPANG PALAY";
replace `y'="SANTA LUCIA" if `y'=="STA. LUCIA";
replace `y'="CABARROGUIS" if `y'=="CABARRUGUIS";
replace `y'="PARANAQUE" if `y'=="TAMBO";
replace `y'="MALABON" if `y'=="POTRERO";
replace `y'="PASIG" if `y'=="CANIOGAN";
replace `y'="MAKATI" if `y'=="LA PAZ--SINGAMAS-TEJEROS";
replace `y'="MAKATI" if `y'=="GUADALUPE NUEVO";
replace `y'="MANDALUYONG" if `y'=="SHAW BOULEVARD";
replace `y'="VILLAVERDE" if `y'=="VILLA VERDE (Ibung)";
replace `y'="LEBAK" if `y'=="LEBAK (Salaman)";
replace `y'="ZARAGOZA" if `y'=="ZARAGOSA";
replace `y'="GENERAL MAMERTO NATIVIDAD" if `y'=="GEN. M. NATIVIDAD";
replace `y'="PRESIDENT QUIRINO" if `y'=="PRES. QUIRINO";
replace `y'="MAKATI" if `y'=="PIO DEL PILAR";
replace `y'="HAGONOY" if `y'=="HAGUNOY";

replace `y'="" if `y'=="UNCLASSIFIED";
replace `y'="" if `y'=="PROVINCE";
replace `y'="TUGUEGARAO CITY" if `y'=="TUGUGARAO CITY";
replace `y'="MANILA" if `y'=="PANDACAN";
replace `y'="POZZORUBIO" if `y'=="POZORRUBIO";
replace `y'="TACURONG CITY" if `y'=="TACURONG";
replace `y'="SCIENCE CITY OF MUNOZ" if substr(`y',1,2)=="MU"&substr(`y',4,2)=="OZ";
replace `y'="LAS PINAS" if substr(`y',1,6)=="LAS PI";
replace `y'="PENABLANCA" if substr(`y',1,2)=="PE"&substr(`y',5,6)=="BLANCA";
replace `y'="VALENZUELA" if `y'=="MALANDAY";
replace `y'="DASMARINAS" if substr(`y',1,7)=="DASMARI";
replace `y'="KABANKALAN CITY" if `y'=="KABANGKALAN CITY";
replace `y'="PARANAQUE" if substr(`y',1,4)=="PARA"&substr(`y',6,9)=="AQUE";
replace `y'="PARANAQUE" if `y'=="PARANQUE CITY";
replace `y'="MAKATI" if `y'=="LA PAZ--SINGKAMAS-TEJEROS";
replace `y'="GENERAL TINIO" if `y'=="GEN. TINIO";
replace `y'="SINDANGAN" if `y'=="SINDANGAN (Leon B. Postigo)";
replace `y'="CABADBARAN CITY" if `y'=="CABADBARAN";
replace `y'="QUEZON CITY" if `y'=="FAIRVIEW NORTH";
replace `y'="SANCHEZ-MIRA" if `y'=="SANCHEZ MIRA";
replace `y'="ESCALANTE CITY" if `y'=="ESCALANTE";
replace `y'="VALENZUELA" if `y'=="DALANDAN-WEST CANUMAY";
replace `y'="DATU ODIN SINSUAT" if `y'=="DINAIG";
replace `y'="SANTO NINO" if substr(`y',1,7)=="STO. NI";
replace `y'="PARANAQUE" if substr(`y',1,4)=="PARA"&substr(`y',-3,3)=="CPO";
replace `y'="DASMARINAS" if `y'=="DASMARIAS";

replace `y'="SIPALAY CITY" if `y'== "SIPALAY";
replace `y'="BORONGAN CITY" if `y'== "BORONGAN";
replace `y'="EL SALVADOR CITY" if `y'== "EL SALVADOR";
replace `y'="SHARIFF AGUAK" if `y'== "MAGANOY";
replace `y'="TANDAG CITY" if `y'== "TANDAG";
};

replace prov="COTABATO" if muni=="KIDAPAWAN CITY";
replace birth_prov="COTABATO" if birth_muni=="KIDAPAWAN CITY";

replace birth_muni="COTABATO CITY" if birth_prov=="COTABATO CITY";
replace birth_prov="MAGUINDANAO" if birth_prov=="COTABATO CITY";
replace birth_muni="MARAWI CITY" if birth_prov=="MARAWI CITY";
replace birth_prov="LANAO DEL SUR" if birth_prov=="MARAWI CITY";
replace birth_muni="MARAWI CITY" if birth_prov=="COTABATO CITY / MARAWI CITY";
replace birth_prov="LANAO DEL SUR" if birth_prov=="COTABATO CITY / MARAWI CITY";
replace birth_muni="" if birthplace=="SAMAR  (WESTERN SAMAR)";
replace birth_prov="SAMAR" if birthplace=="SAMAR  (WESTERN SAMAR)";
replace birth_muni="SHARIFF AGUAK" if birthplace=="SHARIFF AGUAK (MAGANOY) (CAPITAL), (MAGUINDANAO)";
replace birth_prov="MAGUINDANAO" if birthplace=="SHARIFF AGUAK (MAGANOY) (CAPITAL), (MAGUINDANAO)";

replace muni="COTABATO CITY" if prov=="COTABATO CITY";
replace prov="MAGUINDANAO" if prov=="COTABATO CITY";
replace muni="MARAWI CITY" if prov=="MARAWI CITY";
replace prov="LANAO DEL SUR" if prov=="MARAWI CITY";
replace muni="MARAWI CITY" if prov=="COTABATO CITY / MARAWI CITY";
replace prov="LANAO DEL SUR" if prov=="COTABATO CITY / MARAWI CITY";
replace muni="" if addressinthephilippines=="SAMAR  (WESTERN SAMAR)";
replace prov="SAMAR" if addressinthephilippines=="SAMAR  (WESTERN SAMAR)";
replace muni="SHARIFF AGUAK" if addressinthephilippines=="SHARIFF AGUAK (MAGANOY) (CAPITAL), (MAGUINDANAO)";
replace prov="MAGUINDANAO" if addressinthephilippines=="SHARIFF AGUAK (MAGANOY) (CAPITAL), (MAGUINDANAO)";

replace birth_prov="SAMAR" if birthplace=="ALMAGRO, (SAMAR  (WESTERN SAMAR))"
| birthplace=="BASEY, (SAMAR  (WESTERN SAMAR))"
| birthplace=="CALBAYOG CITY, (SAMAR  (WESTERN SAMAR))"
| birthplace=="CALBIGA, (SAMAR  (WESTERN SAMAR))"
| birthplace=="DARAM, (SAMAR  (WESTERN SAMAR))"
| birthplace=="GANDARA, (SAMAR  (WESTERN SAMAR))"
| birthplace=="HINABANGAN, (SAMAR  (WESTERN SAMAR))"
| birthplace=="JIABONG, (SAMAR  (WESTERN SAMAR))"
| birthplace=="MARABUT, (SAMAR  (WESTERN SAMAR))"
| birthplace=="MOTIONG, (SAMAR  (WESTERN SAMAR))"
| birthplace=="PAGSANGHAN, (SAMAR  (WESTERN SAMAR))"
| birthplace=="PINABACDAO, (SAMAR  (WESTERN SAMAR))"
| birthplace=="SAN JORGE, (SAMAR  (WESTERN SAMAR))"
| birthplace=="SAN SEBASTIAN, (SAMAR  (WESTERN SAMAR))"
| birthplace=="SANTA MARGARITA, (SAMAR  (WESTERN SAMAR))"
| birthplace=="SANTA RITA, (SAMAR  (WESTERN SAMAR))"
| birthplace=="SANTO NIÑO, (SAMAR  (WESTERN SAMAR))"
| birthplace=="TALALORA, (SAMAR  (WESTERN SAMAR))"
| birthplace=="TARANGNAN, (SAMAR  (WESTERN SAMAR))"
| birthplace=="VILLAREAL, (SAMAR  (WESTERN SAMAR))"
| birthplace=="ZUMARRAGA, (SAMAR  (WESTERN SAMAR))"
| birthplace=="SAN JOSE DE BUAN, (SAMAR  (WESTERN SAMAR))"
| birthplace=="TAGAPUL-AN, (SAMAR  (WESTERN SAMAR))"
| birthplace=="MATUGUINAO, (SAMAR  (WESTERN SAMAR))";

replace prov="SAMAR" if addressinthephilippines=="ALMAGRO, (SAMAR  (WESTERN SAMAR))"
| addressinthephilippines=="BASEY, (SAMAR  (WESTERN SAMAR))"
| addressinthephilippines=="CALBAYOG CITY, (SAMAR  (WESTERN SAMAR))"
| addressinthephilippines=="CALBIGA, (SAMAR  (WESTERN SAMAR))"
| addressinthephilippines=="DARAM, (SAMAR  (WESTERN SAMAR))"
| addressinthephilippines=="GANDARA, (SAMAR  (WESTERN SAMAR))"
| addressinthephilippines=="HINABANGAN, (SAMAR  (WESTERN SAMAR))"
| addressinthephilippines=="JIABONG, (SAMAR  (WESTERN SAMAR))"
| addressinthephilippines=="MARABUT, (SAMAR  (WESTERN SAMAR))"
| addressinthephilippines=="MOTIONG, (SAMAR  (WESTERN SAMAR))"
| addressinthephilippines=="PAGSANGHAN, (SAMAR  (WESTERN SAMAR))"
| addressinthephilippines=="PINABACDAO, (SAMAR  (WESTERN SAMAR))"
| addressinthephilippines=="SAN JORGE, (SAMAR  (WESTERN SAMAR))"
| addressinthephilippines=="SAN SEBASTIAN, (SAMAR  (WESTERN SAMAR))"
| addressinthephilippines=="SANTA MARGARITA, (SAMAR  (WESTERN SAMAR))"
| addressinthephilippines=="SANTA RITA, (SAMAR  (WESTERN SAMAR))"
| addressinthephilippines=="SANTO NIÑO, (SAMAR  (WESTERN SAMAR))"
| addressinthephilippines=="TALALORA, (SAMAR  (WESTERN SAMAR))"
| addressinthephilippines=="TARANGNAN, (SAMAR  (WESTERN SAMAR))"
| addressinthephilippines=="VILLAREAL, (SAMAR  (WESTERN SAMAR))"
| addressinthephilippines=="ZUMARRAGA, (SAMAR  (WESTERN SAMAR))"
| addressinthephilippines=="SAN JOSE DE BUAN, (SAMAR  (WESTERN SAMAR))"
| addressinthephilippines=="TAGAPUL-AN, (SAMAR  (WESTERN SAMAR))"
| addressinthephilippines=="MATUGUINAO, (SAMAR  (WESTERN SAMAR))";

replace birth_muni="ALMAGRO" if birthplace=="ALMAGRO, (SAMAR  (WESTERN SAMAR))";
replace birth_muni="BASEY" if birthplace=="BASEY, (SAMAR  (WESTERN SAMAR))";
replace birth_muni="CALBAYOG CITY" if birthplace=="CALBAYOG CITY, (SAMAR  (WESTERN SAMAR))";
replace birth_muni="CALBIGA" if birthplace=="CALBIGA, (SAMAR  (WESTERN SAMAR))";
replace birth_muni="DARAM" if birthplace=="DARAM, (SAMAR  (WESTERN SAMAR))";
replace birth_muni="GANDARA" if birthplace=="GANDARA, (SAMAR  (WESTERN SAMAR))";
replace birth_muni="HINABANGAN" if birthplace=="HINABANGAN, (SAMAR  (WESTERN SAMAR))";
replace birth_muni="JIABONG" if birthplace=="JIABONG, (SAMAR  (WESTERN SAMAR))";
replace birth_muni="MARABUT" if birthplace=="MARABUT, (SAMAR  (WESTERN SAMAR))";
replace birth_muni="MOTIONG" if birthplace=="MOTIONG, (SAMAR  (WESTERN SAMAR))";
replace birth_muni="PAGSANGHAN" if birthplace=="PAGSANGHAN, (SAMAR  (WESTERN SAMAR))";
replace birth_muni="PINABACDAO" if birthplace=="PINABACDAO, (SAMAR  (WESTERN SAMAR))";
replace birth_muni="SAN JORGE" if birthplace=="SAN JORGE, (SAMAR  (WESTERN SAMAR))";
replace birth_muni="SAN SEBASTIAN" if birthplace=="SAN SEBASTIAN, (SAMAR  (WESTERN SAMAR))";
replace birth_muni="SANTA MARGARITA" if birthplace=="SANTA MARGARITA, (SAMAR  (WESTERN SAMAR))";
replace birth_muni="SANTA RITA" if birthplace=="SANTA RITA, (SAMAR  (WESTERN SAMAR))";
replace birth_muni="SANTO NINO" if birthplace=="SANTO NIÑO, (SAMAR  (WESTERN SAMAR))";
replace birth_muni="TALALORA" if birthplace=="TALALORA, (SAMAR  (WESTERN SAMAR))";
replace birth_muni="TARANGNAN" if birthplace=="TARANGNAN, (SAMAR  (WESTERN SAMAR))";
replace birth_muni="VILLAREAL" if birthplace=="VILLAREAL, (SAMAR  (WESTERN SAMAR))";
replace birth_muni="ZUMARRAGA" if birthplace=="ZUMARRAGA, (SAMAR  (WESTERN SAMAR))";
replace birth_muni="SAN JOSE DE BUAN" if birthplace=="SAN JOSE DE BUAN, (SAMAR  (WESTERN SAMAR))";
replace birth_muni="TAGAPUL-AN" if birthplace=="TAGAPUL-AN, (SAMAR  (WESTERN SAMAR))";
replace birth_muni="MATUGUINAO" if birthplace=="MATUGUINAO, (SAMAR  (WESTERN SAMAR))";

replace muni="ALMAGRO" if addressinthephilippines=="ALMAGRO, (SAMAR  (WESTERN SAMAR))";
replace muni="BASEY" if addressinthephilippines=="BASEY, (SAMAR  (WESTERN SAMAR))";
replace muni="CALBAYOG CITY" if addressinthephilippines=="CALBAYOG CITY, (SAMAR  (WESTERN SAMAR))";
replace muni="CALBIGA" if addressinthephilippines=="CALBIGA, (SAMAR  (WESTERN SAMAR))";
replace muni="DARAM" if addressinthephilippines=="DARAM, (SAMAR  (WESTERN SAMAR))";
replace muni="GANDARA" if addressinthephilippines=="GANDARA, (SAMAR  (WESTERN SAMAR))";
replace muni="HINABANGAN" if addressinthephilippines=="HINABANGAN, (SAMAR  (WESTERN SAMAR))";
replace muni="JIABONG" if addressinthephilippines=="JIABONG, (SAMAR  (WESTERN SAMAR))";
replace muni="MARABUT" if addressinthephilippines=="MARABUT, (SAMAR  (WESTERN SAMAR))";
replace muni="MOTIONG" if addressinthephilippines=="MOTIONG, (SAMAR  (WESTERN SAMAR))";
replace muni="PAGSANGHAN" if addressinthephilippines=="PAGSANGHAN, (SAMAR  (WESTERN SAMAR))";
replace muni="PINABACDAO" if addressinthephilippines=="PINABACDAO, (SAMAR  (WESTERN SAMAR))";
replace muni="SAN JORGE" if addressinthephilippines=="SAN JORGE, (SAMAR  (WESTERN SAMAR))";
replace muni="SAN SEBASTIAN" if addressinthephilippines=="SAN SEBASTIAN, (SAMAR  (WESTERN SAMAR))";
replace muni="SANTA MARGARITA" if addressinthephilippines=="SANTA MARGARITA, (SAMAR  (WESTERN SAMAR))";
replace muni="SANTA RITA" if addressinthephilippines=="SANTA RITA, (SAMAR  (WESTERN SAMAR))";
replace muni="SANTO NINO" if addressinthephilippines=="SANTO NIÑO, (SAMAR  (WESTERN SAMAR))";
replace muni="TALALORA" if addressinthephilippines=="TALALORA, (SAMAR  (WESTERN SAMAR))";
replace muni="TARANGNAN" if addressinthephilippines=="TARANGNAN, (SAMAR  (WESTERN SAMAR))";
replace muni="VILLAREAL" if addressinthephilippines=="VILLAREAL, (SAMAR  (WESTERN SAMAR))";
replace muni="ZUMARRAGA" if addressinthephilippines=="ZUMARRAGA, (SAMAR  (WESTERN SAMAR))";
replace muni="SAN JOSE DE BUAN" if addressinthephilippines=="SAN JOSE DE BUAN, (SAMAR  (WESTERN SAMAR))";
replace muni="TAGAPUL-AN" if addressinthephilippines=="TAGAPUL-AN, (SAMAR  (WESTERN SAMAR))";
replace muni="MATUGUINAO" if addressinthephilippines=="MATUGUINAO, (SAMAR  (WESTERN SAMAR))";

drop regular regular2 count1 birth_parens address_parens;

label var birth_muni "Birth Municipality";
label var birth_prov "Birth Province";
label var muni "Address Municipality";
label var province "Address Province";

capture tostring yearofbirth age, replace;
replace age="" if age=="NO RESPONSE" | age=="NULL";
destring age, replace;

save "${cleandata}data`w'clean.dta", replace;
};


use "${cleandata}data1988clean.dta", clear;
forvalues w=1989/2014 {;
append using "${cleandata}data`w'clean.dta", force;
};

save "${cleandata}finalCFO.dta", replace;

log close;


